package com.virtualtrainer_01.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

import com.virtualtrainer_01.model.Exercise;
import com.virtualtrainer_01.model.Model;
import com.virtualtrainer_01.model.Muscle;
import com.virtualtrainer_01.util.NameList;

public class ExerciseDB extends SQLiteDB {
	public ExerciseDB(Context c) {
		super(c, DATABASE_TABLE);
	}

	public static final String KEY_ID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_ID_MUSCLE = "id_muscle";
	public static final String KEY_CALORIES = "calories";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_REPS = "reps";
	public static final String KEY_SETS = "sets";
	public static final String KEY_DIFFICULTY = "difficultyRank";

	public static final String DATABASE_TABLE = "exercise";

	@Override
	public long createEntry(Model entry) {
		Exercise e = (Exercise) entry;
		ContentValues cv = new ContentValues();
		cv.put(KEY_NAME, e.getName());
		cv.put(KEY_CALORIES, e.getCalories());
		cv.put(KEY_DESCRIPTION, e.getDescription());
		MuscleDB mdb = new MuscleDB(ourContext);
		mdb.open();
		cv.put(KEY_ID_MUSCLE, mdb.getId(e.getMuscle()));
		mdb.close();
		cv.put(KEY_REPS, e.getReps());
		cv.put(KEY_SETS, e.getSets());
		cv.put(KEY_DIFFICULTY, e.getDifficultyRank());
		return ourDatabase.insert(DATABASE_TABLE, null, cv);
	}

	public String getData() {
		String[] columns = new String[] { KEY_ID, KEY_NAME, KEY_CALORIES,
				KEY_DESCRIPTION, KEY_ID_MUSCLE, KEY_REPS, KEY_SETS,
				KEY_DIFFICULTY };
		Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,
				null, null);
		String result = "";

		int iRow = c.getColumnIndex(KEY_ID);
		int iName = c.getColumnIndex(KEY_NAME);
		int iCalories = c.getColumnIndex(KEY_CALORIES);
		int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
		int iIdGroup = c.getColumnIndex(KEY_ID_MUSCLE);

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
			result = result + c.getString(iRow) + " " + c.getString(iName)
					+ " " + c.getString(iCalories) + " "
					+ c.getString(iDescription) + " " + c.getString(iIdGroup)
					+ "\n";
		return result;
	}

	public Exercise getExercise(String name) {
		String sql = "SELECT * FROM " + DATABASE_TABLE + " t where t.name="
				+ "'" + name + "';";
		Cursor c = ourDatabase.rawQuery(sql, null);

		int iName = c.getColumnIndex(KEY_NAME);
		int iCalories = c.getColumnIndex(KEY_CALORIES);
		int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
		int iSets = c.getColumnIndex(KEY_SETS);
		int iReps = c.getColumnIndex(KEY_REPS);
		int iDif = c.getColumnIndex(KEY_DIFFICULTY);

		Exercise ex = new Exercise();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			ex.setName(c.getString(iName));
			ex.setDescription(c.getString(iDescription));
			ex.setCalories(Double.parseDouble(c.getString(iCalories)));
			ex.setSets(Integer.parseInt(c.getString(iSets)));
			ex.setReps(Integer.parseInt(c.getString(iReps)));
			ex.setReps(Integer.parseInt(c.getString(iDif)));
		}
		return ex;
	}

	public String[] getExerciseNames(String groupName) {
		String sql = "SELECT e." + KEY_NAME + " FROM " + DATABASE_TABLE
				+ " e JOIN " + MuscleDB.DATABASE_TABLE + " m ON m."
				+ MuscleDB.KEY_ID + "=e." + ExerciseDB.KEY_ID_MUSCLE + " JOIN "
				+ GroupDB.DATABASE_TABLE + " g ON g." + GroupDB.KEY_ID + "=m."
				+ MuscleDB.KEY_ID_GROUP + " WHERE g." + GroupDB.KEY_NAME + "='"
				+ groupName + "';";

		Cursor c = ourDatabase.rawQuery(sql, null);
		int iName = c.getColumnIndex(KEY_NAME);
		List<String> lista = new ArrayList<String>();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext())
			lista.add(c.getString(iName));
		String[] list = new String[lista.size()];
		for (int i = 0; i < list.length; i++)
			list[i] = lista.get(i);
		return list;
	}

	public Exercise getExercise(int id) {
		String sql = "SELECT * FROM " + DATABASE_TABLE + " t where t." + KEY_ID
				+ "=" + "'" + id + "';";
		Cursor c = ourDatabase.rawQuery(sql, null);

		int iName = c.getColumnIndex(KEY_NAME);
		int iCalories = c.getColumnIndex(KEY_CALORIES);
		int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
		int iSets = c.getColumnIndex(KEY_SETS);
		int iReps = c.getColumnIndex(KEY_REPS);
		int iDif = c.getColumnIndex(KEY_DIFFICULTY);

		Exercise ex = new Exercise();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			ex.setName(c.getString(iName));
			ex.setDescription(c.getString(iDescription));
			ex.setCalories(Double.parseDouble(c.getString(iCalories)));
			ex.setSets(Integer.parseInt(c.getString(iSets)));
			ex.setReps(Integer.parseInt(c.getString(iReps)));
			ex.setReps(Integer.parseInt(c.getString(iDif)));
		}
		return ex;
	}

	public NameList<Exercise> getExerciseList() {
		String sql = "SELECT * FROM " + DATABASE_TABLE + " t;";
		Cursor c = ourDatabase.rawQuery(sql, null);

		int iName = c.getColumnIndex(KEY_NAME);
		int iCalories = c.getColumnIndex(KEY_CALORIES);
		int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
		int iSets = c.getColumnIndex(KEY_SETS);
		int iReps = c.getColumnIndex(KEY_REPS);
		int iDif = c.getColumnIndex(KEY_DIFFICULTY);

		NameList<Exercise> exList = new NameList<Exercise>();
		Exercise ex = new Exercise();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			ex.setName(c.getString(iName));
			ex.setDescription(c.getString(iDescription));
			ex.setCalories(Double.parseDouble(c.getString(iCalories)));
			ex.setSets(Integer.parseInt(c.getString(iSets)));
			ex.setReps(Integer.parseInt(c.getString(iReps)));
			ex.setReps(Integer.parseInt(c.getString(iDif)));
			exList.add(ex);
		}
		return exList;
	}

	public NameList<Exercise> getExerciseList(String group, String muscle) {
		String sql1 = "", sql2 = "";

		if (!group.equals("")) {
			GroupDB gdb = new GroupDB(ourContext);
			gdb.open();
			int idGroup = gdb.getId(group);
			gdb.close();
			sql1 = " INNER JOIN " + MuscleDB.DATABASE_TABLE + " m ON m."
					+ MuscleDB.KEY_ID + " = t." + KEY_ID_MUSCLE + " JOIN "
					+ GroupDB.DATABASE_TABLE + " g ON m."
					+ MuscleDB.KEY_ID_GROUP + " = g." + GroupDB.KEY_ID
					+ " WHERE g." + GroupDB.KEY_ID + " =" + idGroup;
		}

		if (!muscle.equals("")) {
			MuscleDB mdb = new MuscleDB(ourContext);
			mdb.open();
			int idMuscle = mdb.getId(muscle);
			mdb.close();
			sql2 = " AND m." + MuscleDB.KEY_ID + " = " + idMuscle;
		}

		String sql = "SELECT t." + KEY_NAME + ",t." + KEY_CALORIES + ",t."
				+ KEY_DESCRIPTION + ",t." + KEY_DIFFICULTY + ",t." + KEY_REPS
				+ ",t." + KEY_SETS + ",t." + KEY_ID_MUSCLE + " FROM "
				+ DATABASE_TABLE + " t" + sql1 + sql2+ ";";

		Cursor c = ourDatabase.rawQuery(sql, null);

		int iName = c.getColumnIndex(KEY_NAME);
		int iCalories = c.getColumnIndex(KEY_CALORIES);
		int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
		int iSets = c.getColumnIndex(KEY_SETS);
		int iReps = c.getColumnIndex(KEY_REPS);
		int iDif = c.getColumnIndex(KEY_DIFFICULTY);
		int idMuscle = c.getColumnIndex(KEY_ID_MUSCLE);

		NameList<Exercise> exList = new NameList<Exercise>();	
		MuscleDB mdb = new MuscleDB(ourContext);
		mdb.open();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			Exercise ex = new Exercise();
			ex.setName(c.getString(iName));
			ex.setDescription(c.getString(iDescription));
			ex.setCalories(Double.parseDouble(c.getString(iCalories)));
			ex.setSets(Integer.parseInt(c.getString(iSets)));
			ex.setReps(Integer.parseInt(c.getString(iReps)));
			ex.setDifficultyRank(Integer.parseInt(c.getString(iDif)));
			Muscle m = mdb.getMuscle(Integer.parseInt(c.getString(idMuscle)));
			ex.setMuscle(m);
			exList.add(ex);
		}
		mdb.close();
		return exList;
	}

	public NameList<Exercise> getExerciseList(String group) {
		String sql1 = "";

		if (!group.equals("")) {
			GroupDB gdb = new GroupDB(ourContext);
			gdb.open();
			int idGroup = gdb.getId(group);
			gdb.close();
			sql1 = " INNER JOIN " + MuscleDB.DATABASE_TABLE + " m ON m."
					+ MuscleDB.KEY_ID + " = t." + KEY_ID_MUSCLE + " JOIN "
					+ GroupDB.DATABASE_TABLE + " g ON m."
					+ MuscleDB.KEY_ID_GROUP + " = g." + GroupDB.KEY_ID
					+ " WHERE g." + GroupDB.KEY_ID + " =" + idGroup;
		}

		String sql = "SELECT t." + KEY_NAME + ",t." + KEY_CALORIES + ",t."
				+ KEY_DESCRIPTION + ",t." + KEY_DIFFICULTY + ",t." + KEY_REPS
				+ ",t." + KEY_SETS + ",t." + KEY_ID_MUSCLE + " FROM "
				+ DATABASE_TABLE + " t" + sql1 + ";";

		Cursor c = ourDatabase.rawQuery(sql, null);

		int iName = c.getColumnIndex(KEY_NAME);
		int iCalories = c.getColumnIndex(KEY_CALORIES);
		int iDescription = c.getColumnIndex(KEY_DESCRIPTION);
		int iSets = c.getColumnIndex(KEY_SETS);
		int iReps = c.getColumnIndex(KEY_REPS);
		int iDif = c.getColumnIndex(KEY_DIFFICULTY);
		int idMuscle = c.getColumnIndex(KEY_ID_MUSCLE);

		NameList<Exercise> exList = new NameList<Exercise>();
		MuscleDB mdb = new MuscleDB(ourContext);
		mdb.open();
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
			Exercise ex = new Exercise();
			ex.setName(c.getString(iName));
			ex.setDescription(c.getString(iDescription));
			ex.setCalories(Double.parseDouble(c.getString(iCalories)));
			ex.setSets(Integer.parseInt(c.getString(iSets)));
			ex.setReps(Integer.parseInt(c.getString(iReps)));
			ex.setDifficultyRank(Integer.parseInt(c.getString(iDif)));
			Muscle m = mdb.getMuscle(Integer.parseInt(c.getString(idMuscle)));
			ex.setMuscle(m);
			exList.add(ex);
		}
		mdb.close();
		return exList;
	}
}
